import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mysql.jdbc.MySQLConnection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import com.mysql.jdbc.util.ResultSetUtil;

public class DBConnection {

	final String hostname;
	final String port;
	final String dbname;
	final String user;
	final String password;
	Connection conn;
	// PreparedStatements für page, revision und page_revision

	String page_sql = "INSERT INTO page (pageid, title, touched) VALUES(?, ?, ?)";
	String revision_sql = "INSERT INTO revision (revisionid, timestamp, user, userid, flag) VALUES(?, ?, ?, ?, ?)";
	String page_revision_sql = "INSERT INTO page_revision (pageid, revisionid) VALUES(?, ?)";

	public String getPage_sql() {
		return page_sql;
	}

	public String getRevision_sql() {
		return revision_sql;
	}

	public String getPage_revision_sql() {
		return page_revision_sql;
	}

	DBConnection() {
		this.hostname = "localhost";
		this.port = "3306";
		this.dbname = "android";
		this.user = "root";
		this.password = "";
		connect();
		

	}

	private void connect() {

		try {
			// driverjar // muss aus jar geladen werden
			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
			// connect
			String url = "jdbc:mysql://" + hostname + ":" + port + "/" + dbname;
			conn = DriverManager.getConnection(url, user, password);
			// muss für batched verarbeitung auf false gesetzt sein
			conn.setAutoCommit(false);
		} catch (Exception e) {
			System.out.println(e.getMessage());

		}

	}
// diese methode nimmt einträge auf die Datenbank vor, die Werte werden vom Parser übergeben
	public void commitOnDB(String[][] values, String sql) {
		try {
			
			PreparedStatement stmt = (PreparedStatement) conn
					.prepareStatement(sql);
			// erzeugen eines Prepared Statement

			// autocommit muss fuer den batch auftrag auf false gesetzt werden
			

			Connection myconn = stmt.getConnection();
			if (myconn.isClosed())
				System.out.println(" connection somehow closed");
			

			// loop aller values in die felder
			for (int row = 0; row < values.length; row++) {
				for (int column = 0; column < values[row].length; column++) {
					stmt.setString(column + 1, values[row][column]);
					 System.out.println(values[row][column]);
				}
				stmt.addBatch();

			}

			// execute
			int[] count = stmt.executeBatch();
			for (int n = 0; n < count.length; n++)
				if (count[n]!=1) {
					System.out.println("Eintrag " + count[n] + "wurde nicht ausgefuehrt");
					}
				
			conn.commit();
			// commit

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	
	public ArrayList<String[]> select(String[] koords) {
// koords [0] = lat koords[1] = lon
		ArrayList<String[]> list = null ;
		try {
			 list = new ArrayList<>();
			Statement stmt =  (Statement) conn.createStatement();
			String lat = koords[0];
			String lon = koords[1];
			Double latti = Double.valueOf(lat);
			Double longi = Double.valueOf(lon);
			
			String sql= "SELECT latitude, longitude, ssid, capability FROM `ourtable` WHERE longitude between " +String.valueOf(longi-1)+ " and " +String.valueOf(longi+1) +" and latitude between " +String.valueOf(latti-1) +" and "+String.valueOf(latti+1)+";";
			ResultSet set = stmt.executeQuery(sql);
			int spalten = set.getMetaData().getColumnCount();
			if(set.next() && set.getString("ssid") != null){
			while (set.next()){
				String[] str = new String[4];
				for (int k =1; k<= spalten; k++)
				{
					str[k-1] = set.getString(k);
					
				}
				list.add(str);
			}
			}
			else{
				System.out.println("keine Daten");
			return null;
			}
		
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		

		// returns data from db
		return list;
	}

	public void disconnect() {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}